Redshiftでテーブルのカラム番号・名前・型・桁数を抽出する

Redshiftでテーブルのカラム番号・名前・型・桁数を抽出する

Clock Icon2023.02.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部のueharaです。

今回はAmazon Redshiftでテーブルのカラム番号・名前・型・桁数を抽出するという処理をしてみたいと思います。

方法1

SVV_COLUMNSを利用して、以下のように書くことができます。

SELECT 
  table_schema AS schema_name,
  table_name,
  ordinal_position AS column_number,
  column_name,
  data_type,
  character_maximum_length,
  numeric_precision,
  numeric_scale
FROM SVV_COLUMNS
-- 確認不要なスキーマをフィルタ
WHERE table_schema NOT IN ('admin', 'information_schema', 'pg_catalog')
ORDER BY schema_name, table_name, column_number

結果は、例えば次のようになります。

schema_name table_name column_number column_name data_type character_maximum_length numeric_precision numeric_scale
schema_a table_a 1 column_1 character varying 16 [NULL] [NULL]
schema_a table_a 2 column_2 date [NULL] [NULL] [NULL]
schema_a table_a 3 column_3 numeric [NULL] 32 2
schema_a table_b 1 column_1 integer [NULL] 32 0

NUMERIC(precision, scale)といった、precisionとscaleの桁数をそれぞれカラムを分けて表示したい場合は、こちらを使うと良いと思います。

該当しない項目(例:VARCHARにおけるnumeric_precision等)にはNULLが入ります。

方法2

pg_attribute, pg_class, pg_namespaceを利用して、以下のように書くことができます。

SELECT
  ns.nspname AS schema_name,
  cls.relname AS table_name,
  atr.attnum AS column_number,
  atr.attname AS column_name,
  pg_catalog.format_type(atr.atttypid, atr.atttypmod) AS data_type
FROM pg_attribute atr
JOIN pg_class cls ON atr.attrelid = cls.oid
JOIN pg_namespace ns ON cls.relnamespace = ns.oid
WHERE
  column_number > 0 AND
  NOT atr.attisdropped AND
  -- 確認不要なスキーマをフィルタ
  schema_name NOT LIKE 'pg_%' AND
  schema_name NOT IN ('admin', 'information_schema')
ORDER BY schema_name, table_name, column_number

結果は、例えば次のようになります。

schema_name table_name column_number column_name data_type
schema_a table_a 1 column_1 character varying(16)
schema_a table_a 2 column_2 date
schema_a table_a 3 column_3 numeric(32,2)
schema_a table_b 1 column_1 integer

データ型と桁数を同じカラム内に表現したい場合は、こちらを使うと良いと思います。

その他の方法

カラムの型・桁を抽出したいテーブルが属するスキーマがsearch_pathに設定されている場合、PG_TABLE_DEFを利用して次のように書くこともできます。

set_pathについては公式ドキュメントをご確認下さい。

WITH tmp AS (
  SELECT 
    table_schema AS schema_name,
    table_name,
    ordinal_position AS column_number,
    column_name
  FROM SVV_COLUMNS
)
SELECT
  schemaname AS schema_name,
  tablename AS table_name,
  column_number,
  "column" AS column_name,
  type AS data_type
FROM PG_TABLE_DEF ptd
JOIN tmp ON
  ptd.schemaname = tmp.schema_name AND
  ptd.tablename = tmp.table_name AND
  ptd."column" = tmp.column_name
-- 確認不要なスキーマをフィルタ
WHERE schema_name NOT IN ('pg_catalog')
ORDER BY schema_name, table_name, column_number

結果は「方法2」の表示結果と同じになります。

schema_name table_name column_number column_name data_type
schema_a table_a 1 column_1 character varying(16)
schema_a table_a 2 column_2 date
schema_a table_a 3 column_3 numeric(32,2)
schema_a table_b 1 column_1 integer

最後に

今回はAmazon Redshiftのテーブルのカラム番号・名前・型・桁数を抽出するを抽出するという処理をしてみました。

参考になりましたら幸いです。

参考文献

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.